package org.mcflier.appuntamento.db;

import java.beans.PropertyVetoException;
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.ServerSocket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Logger;

import org.h2.tools.Server;
import org.mcflier.appuntamento.core.Factory;
import org.mcflier.appuntamento.core.exception.ModulesException;
import org.mcflier.appuntamento.core.exception.PropertiesException;
import org.mcflier.appuntamento.core.logger.LoggerManager;
import org.mcflier.appuntamento.core.modules.IModule;
import org.mcflier.appuntamento.core.modules.ModuleNode;
import org.mcflier.appuntamento.core.properties.PropertiesManager;
import org.mcflier.appuntamento.db.exception.DatabaseConnectionException;
import org.mcflier.appuntamento.db.exception.DatabaseServerException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBManager implements IModule{
	
	private static final String USER_TAG = "user";
	private static final String PASSWORD_TAG = "password";
	private static final String DRIVER_CLASS_TAG = "jdbc_driver";
	private static final String TCP_PORT_TAG = "tcpPort";
	private static final String TCP_ALLOW_OTHERS_TAG = "tcpAllowOthers";
	private static final String WEB_PORT_TAG = "webPort";
	private static final String WEB_ALLOW_OTHERS_TAG = "webAllowOthers";
	private static final String CREATE_SCRIPT_TAG = "create_script_file";
	private static final String INSERT_SCRIPT_TAG = "insert_script_file";
	private static final String DATABASE_NAME_TAG = "databaseName";
	private static final String URL_PREFIX = "jdbc_url_prefix";
	
	private static final String MIN_POOL_CONNS_TAG = "minPoolConns";
	private static final String MAX_POOL_CONNS_TAG = "maxPoolConns";
	private static final String INCREMENT_POOL_COONS_TAG = "incrementPoolConns";
	private static final String MAX_IDLE_TAG = "maxIdle";
	
	private Factory factory;
	private Properties properties;
	private PropertiesManager propertiesManager;
	private Logger logger;
	
	private Server tcp;
	private Server web;
	
	private String user, password, baseDir, tcpPort, tcpAllowOthers, webPort, webAllowOthers, driverClass, jdbcUrl;
	
	private ComboPooledDataSource pool;
	
	public DBManager() throws ModulesException{
		this.factory = Factory.getInstance();
		this.propertiesManager = ((PropertiesManager)this.factory.getModuleInstance(Factory.MODULE_PROPERTIES));
		this.properties = this.propertiesManager.getProperties();
		this.logger = ((LoggerManager)this.factory.getModuleInstance(Factory.MODULE_LOGGER)).getLogger(this.getClass().getName());
	}
	
	private void initPool() throws PropertiesException{
		int min, max, inc, idle;
		try{
			if(this.properties.getProperty(MIN_POOL_CONNS_TAG)==null){
				this.logger.warning("Property '"+MIN_POOL_CONNS_TAG+"' is missing. Used default value");
				min = 5;
			}
			else
				min = Integer.parseInt(this.properties.getProperty(MIN_POOL_CONNS_TAG));
			
			if(this.properties.getProperty(MAX_POOL_CONNS_TAG)==null){
				this.logger.warning("Property '"+MAX_POOL_CONNS_TAG+"' is missing. Used default value");
				max = 10;
			}
			else
				max = Integer.parseInt(this.properties.getProperty(MAX_POOL_CONNS_TAG));
			
			if(this.properties.getProperty(INCREMENT_POOL_COONS_TAG)==null){
				this.logger.warning("Property '"+INCREMENT_POOL_COONS_TAG+"' is missing. Used default value");
				inc = 2;
			}
			else
				inc = Integer.parseInt(this.properties.getProperty(INCREMENT_POOL_COONS_TAG));
			
			if(this.properties.getProperty(MAX_IDLE_TAG)==null){
				this.logger.warning("Property '"+MAX_IDLE_TAG+"' is missing. Used default value");
				idle = 60;
			}
			else
				idle = Integer.parseInt(this.properties.getProperty(MAX_IDLE_TAG));
		}
		catch(NumberFormatException except){
			this.logger.warning("The property values for '"+MAX_IDLE_TAG+"', '"+MIN_POOL_CONNS_TAG+"', '"+MAX_POOL_CONNS_TAG+"' and '"+INCREMENT_POOL_COONS_TAG+"' must be greater than zero. Used default values");
			min = 5;
			max = 10;
			inc = 2;
			idle = 60;
		}
		
		this.pool = new ComboPooledDataSource();
		try {
			this.pool.setDriverClass(driverClass);
			this.pool.setJdbcUrl(this.jdbcUrl);
			this.pool.setUser(this.user);
			this.pool.setPassword(this.password);
			this.pool.setMinPoolSize(min);
			this.pool.setMaxPoolSize(max);
			this.pool.setAcquireIncrement(inc);
			this.pool.setMaxIdleTime(idle);
			this.pool.setMaxConnectionAge(60);
		} catch (PropertyVetoException e) {
			throw new PropertiesException("Error creating the jdbc pool: "+e.getMessage(), e);
		}
		
	}
	
	private void createTCPServer() throws SQLException{
		this.tcp = Server.createTcpServer("-tcpPassword", password, "-tcpPort", tcpPort, (Boolean.parseBoolean(tcpAllowOthers))?"-tcpAllowOthers":"", "-baseDir", this.baseDir);
	}
	
	private void createWebServer() throws SQLException{
		ArrayList<String> args = new ArrayList<String>();
		args.add("-webPort");
		args.add(this.webPort);
		if(Boolean.parseBoolean(this.webAllowOthers))
			args.add("-webAllowOthers");
		args.add("-baseDir");
		args.add(this.baseDir);
		String[] array = new String[args.size()];
		args.toArray(array);
		this.web = Server.createWebServer(array);
	}
	
	private boolean checkPort(int port){
		try {
			ServerSocket server = new ServerSocket(port);
			server.close();
		} catch (IOException e) {
			return false;
		}
		
		return true;
	}
	
	private int getFreePort(){
		try {
			ServerSocket server = new ServerSocket(0);
			int port = server.getLocalPort();
			server.close();
			return port;
		} catch (IOException e) {
			return 0;
		}
	}
	
	public void startDatabase() throws PropertiesException, DatabaseServerException{
		this.user = this.properties.getProperty(USER_TAG);
		if(this.user == null)
			throw new PropertiesException("Error. The property '"+USER_TAG+"' is not present");
		
		this.password = this.properties.getProperty(PASSWORD_TAG);
		if(this.password == null)
			throw new PropertiesException("Error. The property '"+PASSWORD_TAG+"' is not present");
		
		this.driverClass = this.properties.getProperty(DRIVER_CLASS_TAG);
		if(this.driverClass == null)
			throw new PropertiesException("Error. The property '"+DRIVER_CLASS_TAG+"' is not present");
		
		this.tcpPort = this.properties.getProperty(TCP_PORT_TAG);
		if(this.tcpPort == null)
			throw new PropertiesException("Error. The property '"+TCP_PORT_TAG+"' is not present");
		else
			try{
				if(Integer.parseInt(this.tcpPort)<=0)
					throw new PropertiesException("The property '"+TCP_PORT_TAG+"' has an illegal value. It must be greater than zero");
			}
			catch(NumberFormatException e){
				throw new PropertiesException("The property '"+TCP_PORT_TAG+"' has an illegal value. It must be a number greater than zero");
			}
		
		this.baseDir = System.getProperty("user.dir")+System.getProperty("file.separator")+"db";
		File dir = new File(this.baseDir);
		if(!dir.exists()) dir.mkdir();
		//this.baseDir = this.baseDir+System.getProperty("file.separator")+this.properties.getProperty(DATABASE_NAME_TAG);
		
		this.tcpAllowOthers = this.properties.getProperty(TCP_ALLOW_OTHERS_TAG);
		if(this.tcpAllowOthers == null)
			throw new PropertiesException("Error. The property '"+TCP_ALLOW_OTHERS_TAG+"' is not present");
		
		this.webPort = this.properties.getProperty(WEB_PORT_TAG);
		if(this.webPort == null)
			throw new PropertiesException("Error. The property '"+WEB_PORT_TAG+"' is not present");
		else
			try{
				if(Integer.parseInt(this.webPort)<=0)
					throw new PropertiesException("The property '"+WEB_PORT_TAG+"' has an illegal value. It must be greater than zero");
			}
			catch(NumberFormatException e){
				throw new PropertiesException("The property '"+WEB_PORT_TAG+"' has an illegal value. It must be a number greater than zero");
			}
		
		this.webAllowOthers = this.properties.getProperty(WEB_ALLOW_OTHERS_TAG);
		if(this.webAllowOthers == null)
			throw new PropertiesException("Error. The property '"+WEB_ALLOW_OTHERS_TAG+"' is not present");
		
		try {
			if(this.checkPort(Integer.parseInt(this.tcpPort))){
					this.createTCPServer();
			}
			else{
				this.tcpPort = String.valueOf(this.getFreePort());
				this.createTCPServer();
				this.propertiesManager.addProperty(TCP_PORT_TAG, this.tcpPort);
				this.propertiesManager.storeProperties();
			}
		} catch (SQLException e) {
			throw new DatabaseServerException("Error creating the tcp database server", e);
		}
		
		try {
			if(this.checkPort(Integer.parseInt(this.webPort))){
				this.createWebServer();
		}
		else{
			this.webPort = String.valueOf(this.getFreePort());
			this.createWebServer();
			this.propertiesManager.addProperty(WEB_PORT_TAG, this.webPort);
			this.propertiesManager.storeProperties();
		}
		} catch (SQLException e) {
			throw new DatabaseServerException("Error creating the web database server", e);
		}
		
		try {
			this.tcp.start();
			this.logger.info("Database TCP server started and listening: "+this.tcp.getStatus());
			try {
				this.web.start();
				this.logger.info("Database WEB server started and listening: "+this.web.getStatus());
			} catch (Exception e) {
				this.logger.warning("Error starting the WEB server.\n"+e.getMessage());
			}
		} catch (SQLException e) {
			throw new DatabaseServerException("Error starting the TCP server", e);
		}
		
		this.jdbcUrl = this.properties.getProperty(URL_PREFIX)+"tcp://localhost:"+this.tcpPort+System.getProperty("file.separator")+this.properties.getProperty(DATABASE_NAME_TAG);
		this.logger.config("The jdbc url for connecting is: "+this.jdbcUrl);
		this.initPool();
	}
	
	public void stopDatabase(){
		this.pool.close();
		
		try {
			synchronized (this.pool) {
				this.pool.wait(this.pool.getMaxPoolSize()*100);
			}
		} catch (InterruptedException e) {
			this.logger.warning("Error waiting for all closures. Some exceptions may be launched");
		}
		
		this.web.stop();
		this.logger.info("Web database server stopped");
		this.tcp.stop();
		this.logger.info("Tcp database server stopped");
	}
	
	public void restartDatabase() throws PropertiesException, DatabaseServerException{
		this.logger.info("Restarting the database server");
		this.stopDatabase();
		this.startDatabase();
	}
	
	public Connection getConnection() throws DatabaseConnectionException{
		this.logger.config("Requesting pool for a connection");
		try {
			return this.pool.getConnection();
		} catch (SQLException e) {
			throw new DatabaseConnectionException("Error getting a connection from pool: "+e.getMessage(), e);
		}
	}
	
	private String[] getLinesFromFile(String file) throws DatabaseServerException{
		if(file != null){
			BufferedReader input = new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream(file)));
			if(input != null){
				String content = "", line = "";
				try {
					while((line=input.readLine())!=null)
							if(!line.startsWith("--"))
								content+=line;
					
					String[] lines = content.split(";");
					return lines;
				} catch (IOException e) {
					throw new DatabaseServerException("Error reading the content of the file: "+file, e);
				}
			}
			else{
				throw new DatabaseServerException("The create script file is missing");
			}
		}
		else
			return null;
	}
	
	public void resetDatabase() throws PropertiesException, DatabaseConnectionException, DatabaseServerException{
		try {
			Connection conn = this.getConnection();
			if(/*!conn.isValid(1) ||*/ conn.isClosed()){
				this.logger.warning("Impossible to access to the database. It will be restarted");
				this.restartDatabase();
				if(!conn.isClosed()) conn.close();

				conn = this.getConnection();
				if(!conn.isValid(1))
					throw new DatabaseServerException("Error on second try for accessing the database. It won't be reset");
			}
			Statement statement = conn.createStatement();
			
			String fileName = this.properties.getProperty(CREATE_SCRIPT_TAG);
			if(fileName!=null){
				String[] lines = this.getLinesFromFile(fileName);
				if(lines!=null){
					for (int i = 0; i < lines.length; i++) {
						this.logger.config("Executing reset statement: "+lines[i]);
						statement.executeUpdate(lines[i]);
					}
				}
				else{
					throw new DatabaseServerException("Error reseting the database. The file '"+fileName+"' cannot be read");
				}
			}
			else{
				throw new PropertiesException("Error reseting the database. The property '"+CREATE_SCRIPT_TAG+"' is missing");
			}
			
			fileName = this.properties.getProperty(INSERT_SCRIPT_TAG);
			if(fileName!=null){
				String[] lines = this.getLinesFromFile(fileName);
				if(lines!=null){
					for (int i = 0; i < lines.length; i++) {
						this.logger.config("Executing reset statement: "+lines[i]);
						statement.executeUpdate(lines[i]);
					}
				}
				else{
					throw new DatabaseServerException("Error reseting the database. The file '"+fileName+"' cannot be read");
				}
			}
			else{
				throw new PropertiesException("Error reseting the database. The property '"+INSERT_SCRIPT_TAG+"' is missing");
			}
			
			statement.close();
			conn.close();
		} catch (SQLException e) {
			throw new DatabaseServerException("Error reseting the database: "+e.getMessage(), e);
		}			
	}

	@Override
	public void receiveModuleContent(ModuleNode moduleContent) {}
}